<?php 
class agency_Model extends Model
{
	function agency_Model()
	{
		parent::Model();
	}
	
	/*Esta función es utilizada para insertar en cualquier tabla en la BD*/
	function insert_BD($table,$data)
	{
		$this->db->insert($table,$data);
	}//end insertBD
	
	function getCities()
	{
		$query = $this->db->query('SELECT P.id_place as id, P.name as name FROM PLACE P WHERE P.fk_Place IS NOT NULL ORDER BY P.name');
		return $query->result_array();	
	}//END getCities
	
	function getTelephones($id_owner,$field)
	{
		$this->db->select('TELEPHONE.type, TELEPHONE.number');
		$this->db->from('TELEPHONE');
		$this->db->where($field,$id_owner);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function getRepresentative($id_agency)
	{
		$this->db->select('REPRESENTATIVE.*');
		$this->db->where('REPRESENTATIVE.fk_travel_agency',$id_agency);
		$this->db->from('REPRESENTATIVE');
		$query = $this->db->get();
		$data = $query->result_array();
		foreach($data as $row)
		{
			$aux = $row['id_representative'];
		}
		return $aux;
	}
	
	function get_agency($id)
	{ 	
		if ($id != NULL)
		{
			$this->db->where('TRAVEL_AGENCY.id_travel_agency',$id);
		}
		$this->db->select('REPRESENTATIVE.name as nameR, REPRESENTATIVE.name2 as name2R, REPRESENTATIVE.lastname as lastnameR, REPRESENTATIVE.lastname as lastname2R, REPRESENTATIVE.email as emailR');
		$this->db->select('PLACE.name as namePlace');
		$this->db->select('TRAVEL_AGENCY.*');
		$this->db->from('TRAVEL_AGENCY');
		$this->db->join('REPRESENTATIVE','REPRESENTATIVE.fk_travel_agency = TRAVEL_AGENCY.id_travel_agency','left');
		$this->db->join('PLACE','PLACE.id_place = TRAVEL_AGENCY.fk_place','left');
		$query = $this->db->get();
		return $query->result_array();
	}//END function get_agency($id);
	
	function get_notification($field,$id,$level)
	{ 	
		if ($id != NULL)
		{
		$this->db->where($field,$id);
		}
		$this->db->where('NOTIFICATION.level',$level);
		$this->db->select('NOTIFICATION.*');
		$this->db->from('NOTIFICATION');
		$this->db->order_by('NOTIFICATION.dateRegister','desc');
		$query = $this->db->get();
		return $query->result_array();
	}//END function get_agency($id);
	
	function get_Hotels($id_hotel,$name,$place,$crs)
	{
		if ($crs != NULL)
		{
			$this->db->where('HOTEL.crs',$crs);
		}
		if ($name != NULL)
		{
			$this->db->like('HOTEL.name',$name);
		}
		if ($id_hotel != NULL)
		{
			$this->db->where('HOTEL.id_hotel',$id_hotel);
		}
		if ($place != ' ')
		{
			$this->db->where('HOTEL.fk_place',$place);
		}
		$this->db->select('HOTEL.*');
		$this->db->select('PLACE.name as namePlace');
		$this->db->from('HOTEL');
		$this->db->order_by('HOTEL.name','desc');
		$this->db->join('PLACE','PLACE.id_place = HOTEL.fk_place','left');
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function getDetails($agency,$hotel)
	{
		$this->db->where('REQUEST_HOTEL_AGENCY.fk_travel_agency',$agency);
		$this->db->where('REQUEST_HOTEL_AGENCY.fk_hotel',$hotel);
		$this->db->select('REQUEST_HOTEL_AGENCY.*');
		$this->db->from('REQUEST_HOTEL_AGENCY');
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_HotelsAfilliatesAgency($agency)
	{
		//$this->db->where('REQUEST_HOTEL_AGENCY.dateConfirm !=', NULL);
		$this->db->where('REQUEST_HOTEL_AGENCY.fk_travel_agency',$agency);
		$this->db->where('REQUEST_HOTEL_AGENCY.status','AFILLIATE');
		$this->db->select('HOTEL.*');
		$this->db->select('PLACE.name as namePlace');
		$this->db->from('HOTEL');
		$this->db->order_by('HOTEL.name','desc');
		$this->db->join('PLACE','PLACE.id_place = HOTEL.fk_place','left');
		$this->db->join('REQUEST_HOTEL_AGENCY','REQUEST_HOTEL_AGENCY.fk_hotel = HOTEL.id_hotel','left');
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function updateData($table,$data,$id_user)
	{
		$this->db->where('USER.id_user',$id_user);
		$this->db->update($table,$data);
	}
	
	function uploadData($table,$data,$id,$field,$field2,$value)
	{
		if($field2 != NULL)
		{
			$this->db->where($field2,$value);
		}
		$this->db->where($field,$id);
		$this->db->update($table,$data);
	}//end update_BD
	
	function get_names_Hotels_autocomplete($agency)
	{
		if ($agency != NULL)
		{
			$this->db->where('REQUEST_HOTEL_AGENCY.fk_travel_agency',$agency);
			$this->db->where('REQUEST_HOTEL_AGENCY.status','AFILLIATE');
			$this->db->join('REQUEST_HOTEL_AGENCY','REQUEST_HOTEL_AGENCY.fk_hotel = HOTEL.id_hotel','left');
		}
		$this->db->where('HOTEL.crs',1);
		$this->db->select('HOTEL.name');
		$this->db->from('HOTEL');
		$this->db->order_by('HOTEL.name','desc');
		$query = $this->db->get();
		$data = $query->result_array();
		$i = 0;
		foreach($data as $row)
		{
		$aux[$i] = $row['name'];
		$i++;
		}
	return $aux;
	}
	
	function selectStatusRequest($agency,$status)
	{
		$this->db->where('REQUEST_HOTEL_AGENCY.fk_travel_agency',$agency);
		$this->db->where('REQUEST_HOTEL_AGENCY.status',$status);
		$this->db->select('REQUEST_HOTEL_AGENCY.fk_hotel as hotelX');
		$this->db->from('REQUEST_HOTEL_AGENCY');
		$query = $this->db->get();
		$data = $query->result_array();
		$i = 0;
		if($data != NULL)
		{
			foreach($data as $row)
			{
				$aux[$i] = $row['hotelX'];
				$i++;
			}
			return $aux;
		}
		else 
		{ 
			$aux = array('hotelX' => 0 ); 
			return $aux;
		}
	}
	
	/*//////////////////////////////////////////////////////////// estas funciones corresponden a la seccion de hoteles del CRS  /////////////////////////////////////*/
	
	function get_AgencyRequest($hotel)
	{
		$this->db->where('REQUEST_HOTEL_AGENCY.dateConfirm', NULL);
		$this->db->where('REQUEST_HOTEL_AGENCY.fk_hotel',$hotel);
		$this->db->where('REQUEST_HOTEL_AGENCY.status','WAITING');
		$this->db->select('TRAVEL_AGENCY.*');
		$this->db->select('PLACE.name as namePlace');
		$this->db->from('TRAVEL_AGENCY');
		$this->db->order_by('TRAVEL_AGENCY.nameTravelagency','desc');
		$this->db->join('PLACE','PLACE.id_place = TRAVEL_AGENCY.fk_place','left');
		$this->db->join('REQUEST_HOTEL_AGENCY','REQUEST_HOTEL_AGENCY.fk_travel_agency = TRAVEL_AGENCY.id_travel_agency','left');
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_AgencyAfilliates($hotel)
	{
		$this->db->where('REQUEST_HOTEL_AGENCY.fk_hotel',$hotel);
		$this->db->where('REQUEST_HOTEL_AGENCY.status','AFILLIATE');
		$this->db->select('TRAVEL_AGENCY.*');
		$this->db->select('PLACE.name as namePlace');
		$this->db->from('TRAVEL_AGENCY');
		$this->db->order_by('TRAVEL_AGENCY.nameTravelagency','desc');
		$this->db->join('PLACE','PLACE.id_place = TRAVEL_AGENCY.fk_place','left');
		$this->db->join('REQUEST_HOTEL_AGENCY','REQUEST_HOTEL_AGENCY.fk_travel_agency = TRAVEL_AGENCY.id_travel_agency','left');
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function getDetailsReservationAgency($agency)
	{
		$this->db->where('RQRESERVATION_HOTEL_AGENCY.fk_travel_agency',$agency);
		$this->db->select('RQRESERVATION_HOTEL_AGENCY.*');
		$this->db->select('HOTEL.name as nameH');
		$this->db->select('TRAVEL_AGENCY.nameTravelagency as nameA');
		$this->db->from('RQRESERVATION_HOTEL_AGENCY');
		$this->db->join('HOTEL','HOTEL.id_hotel = RQRESERVATION_HOTEL_AGENCY.fk_hotel','left');
		$this->db->join('TRAVEL_AGENCY','TRAVEL_AGENCY.id_travel_agency = RQRESERVATION_HOTEL_AGENCY.fk_travel_agency','left');
		$this->db->order_by('RQRESERVATION_HOTEL_AGENCY.dateRequest','desc');
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_HotelAfilliates($agency)
	{
		$this->db->where('REQUEST_HOTEL_AGENCY.fk_travel_agency',$agency);
		$this->db->where('REQUEST_HOTEL_AGENCY.status','AFILLIATE');
		$this->db->select('HOTEL.id_hotel, HOTEL.name');
		$this->db->from('HOTEL');
		$this->db->order_by('HOTEL.name','desc');
		$this->db->join('REQUEST_HOTEL_AGENCY','REQUEST_HOTEL_AGENCY.fk_hotel = HOTEL.id_hotel','left');
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function selectStatusRequestAgencies($hotel,$status)
	{
		$this->db->where('REQUEST_HOTEL_AGENCY.fk_hotel',$hotel);
		$this->db->where('REQUEST_HOTEL_AGENCY.status',$status);
		$this->db->select('REQUEST_HOTEL_AGENCY.fk_travel_agency as agencyX');
		$this->db->from('REQUEST_HOTEL_AGENCY');
		$query = $this->db->get();
		$data = $query->result_array();
		$i = 0;
		if($data != NULL)
		{
			foreach($data as $row)
			{
				$aux[$i] = $row['agencyX'];
				$i++;
			}
			return $aux;
		}
		else 
		{ 
			$aux = array('agencyX' => 0 ); 
			return $aux;
		}
	}
	
	function get_id_plan($id_hotel)
	{
		$this->db->where('PLAN.fk_hotel',$id_hotel);
		$this->db->where('PLAN.disable',0);
		$this->db->select('PLAN.id_plan as id_plan, PLAN.name as name_plan');
		$query = $this->db->get('PLAN');
		return $query->result_array();
	}
	
	function get_id_rate($id_hotel)
	{
		$this->db->where('RATE.fk_hotel',$id_hotel);
		$this->db->where('RATE.disable',0);
		$this->db->select('RATE.id_rate as id_rate, RATE.name as name_rate');
		$query = $this->db->get('RATE');
		return $query->result_array();
	}
	
	function get_id_users($user_creator)
	{
		$this->db->where('USER.userCreator',$user_creator);
    	$this->db->where('USER.disable',0);
		$this->db->select('USER.id_user');
		$query = $this->db->get('USER');
		return $query->result_array();
	}
	
	function flag_update($table,$id,$flag,$field)
	{
		$this->db->where($field, $id);
		$data = array (
				'disable' => $flag
				);
		$this->db->update($table,$data);
	}//END function flag_update
	
}//END class agency_Model
?>